<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>变量与流程控制与游标 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/103.b0e7acf6.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f68b46/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" aria-current="page" class="active sidebar-link">变量与流程控制与游标</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/f68b46/#_1-变量" class="sidebar-link">1. 变量</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_1-1-系统变量" class="sidebar-link">1.1 系统变量</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_1-2-用户变量" class="sidebar-link">1.2 用户变量</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/f68b46/#_2-定义条件与处理程序" class="sidebar-link">2. 定义条件与处理程序</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_2-1-案例分析" class="sidebar-link">2.1 案例分析</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_2-2-定义条件" class="sidebar-link">2.2 定义条件</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_2-3-定义处理程序" class="sidebar-link">2.3 定义处理程序</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_2-4-案例解决" class="sidebar-link">2.4 案例解决</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/f68b46/#_3-流程控制" class="sidebar-link">3. 流程控制</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-1-分支结构之-if" class="sidebar-link">3.1 分支结构之 IF</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-2-分支结构之-case" class="sidebar-link">3.2 分支结构之 CASE</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-3-循环结构之loop" class="sidebar-link">3.3 循环结构之LOOP</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-4-循环结构之while" class="sidebar-link">3.4 循环结构之WHILE</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-5-循环结构之repeat" class="sidebar-link">3.5 循环结构之REPEAT</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-6-跳转语句之leave语句" class="sidebar-link">3.6 跳转语句之LEAVE语句</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_3-7-跳转语句之iterate语句" class="sidebar-link">3.7 跳转语句之ITERATE语句</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/f68b46/#_4-游标" class="sidebar-link">4. 游标</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_4-1-什么是游标-或光标" class="sidebar-link">4.1 什么是游标（或光标）</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_4-2-使用游标步骤" class="sidebar-link">4.2 使用游标步骤</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_4-3-举例" class="sidebar-link">4.3 举例</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/f68b46/#_4-5-小结" class="sidebar-link">4.5 小结</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/f68b46/#补充-mysql-8-0的新特性-全局变量的持久化" class="sidebar-link">补充：MySQL 8.0的新特性—全局变量的持久化</a></li></ul></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">变量与流程控制与游标<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="变量与流程控制与游标"><a href="#变量与流程控制与游标" class="header-anchor">#</a> 变量与流程控制与游标</h1> <h2 id="_1-变量"><a href="#_1-变量" class="header-anchor">#</a> 1. 变量</h2> <p>在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。</p> <p>在 MySQL 数据库中，变量分为 <code>系统变量</code> 以及 <code>用户自定义变量</code> 。</p> <h3 id="_1-1-系统变量"><a href="#_1-1-系统变量" class="header-anchor">#</a> 1.1 系统变量</h3> <h4 id="_1-1-1-系统变量分类"><a href="#_1-1-1-系统变量分类" class="header-anchor">#</a> 1.1.1 系统变量分类</h4> <p>变量由系统定义，不是用户定义，属于 <code>服务器</code> 层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是 <code>编译MySQL时参数</code> 的默认值，要么是 <code>配置文件</code> （例如my.ini等）中的参数值。大家可以通过网址  https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 查看MySQL文档的系统变量。</p> <p>系统变量分为全局系统变量（需要添加 <code>global</code> 关键字）以及会话系统变量（需要添加  <code>session</code> 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。<strong>如果不写，默认会话级别</strong>。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。</p> <p>每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。如下图：</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251930491.png" alt="image-20220613081415624"></p> <ul><li><p>全局系统变量针对于所有会话（连接）有效，但 <code>不能跨重启</code></p></li> <li><p>会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。</p></li> <li><p>会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。</p></li></ul> <p>在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。</p> <h4 id="_1-1-2-查看系统变量"><a href="#_1-1-2-查看系统变量" class="header-anchor">#</a> 1.1.2 查看系统变量</h4> <ul><li><strong>查看所有或部分系统变量</strong></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#查看所有全局变量</span>
<span class="token keyword">SHOW</span> <span class="token keyword">GLOBAL</span> VARIABLES<span class="token punctuation">;</span>

<span class="token comment">#查看所有会话变量</span>
<span class="token keyword">SHOW</span> <span class="token keyword">SESSION</span> VARIABLES<span class="token punctuation">;</span>
或
<span class="token keyword">SHOW</span> VARIABLES<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#查看满足条件的部分系统变量。</span>
<span class="token keyword">SHOW</span> <span class="token keyword">GLOBAL</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%标识符%'</span><span class="token punctuation">;</span>

<span class="token comment">#查看满足条件的部分会话变量</span>
<span class="token keyword">SHOW</span> <span class="token keyword">SESSION</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'%标识符%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> <span class="token keyword">GLOBAL</span> VARIABLES <span class="token operator">LIKE</span> <span class="token string">'admin_%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><strong>查看指定系统变量</strong></li></ul> <p>作为 MySQL 编码规范，MySQL 中的系统变量以 <code>两个“@”</code> 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#查看指定的系统变量的值</span>
<span class="token keyword">SELECT</span> @<span class="token variable">@global.</span>变量名<span class="token punctuation">;</span>

<span class="token comment">#查看指定的会话变量的值</span>
<span class="token keyword">SELECT</span> @<span class="token variable">@session.</span>变量名<span class="token punctuation">;</span>
<span class="token comment">#或者</span>
<span class="token keyword">SELECT</span> @@变量名<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><ul><li><strong>修改系统变量的值</strong></li></ul> <p>有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：</p> <p>方式1：修改MySQL <code>配置文件</code> ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）</p> <p>方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#为某个系统变量赋值</span>
<span class="token comment">#方式1：</span>
<span class="token keyword">SET</span> @<span class="token variable">@global.</span>变量名<span class="token operator">=</span>变量值<span class="token punctuation">;</span>

<span class="token comment">#方式2：</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> 变量名<span class="token operator">=</span>变量值<span class="token punctuation">;</span>

<span class="token comment">#为某个会话变量赋值</span>
<span class="token comment">#方式1：</span>
<span class="token keyword">SET</span> @<span class="token variable">@session.</span>变量名<span class="token operator">=</span>变量值<span class="token punctuation">;</span>

<span class="token comment">#方式2：</span>
<span class="token keyword">SET</span> <span class="token keyword">SESSION</span> 变量名<span class="token operator">=</span>变量值<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> @<span class="token variable">@global.autocommit</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> @<span class="token variable">@session.tx_isolation</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> @<span class="token variable">@session.tx_isolation</span><span class="token operator">=</span><span class="token string">'read-uncommitted'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> @<span class="token variable">@global.max_connections</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_1-2-用户变量"><a href="#_1-2-用户变量" class="header-anchor">#</a> 1.2 用户变量</h3> <h4 id="_1-2-1-用户变量分类"><a href="#_1-2-1-用户变量分类" class="header-anchor">#</a> 1.2.1 用户变量分类</h4> <p>用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 <code>一个“@”</code> 开头。</p> <p>根据作用范围不同，又分为 <code>会话用户变量</code> 和 <code>局部变量</code> 。</p> <ul><li>会话用户变量：作用域和会话变量一样，只对 <code>当前连接</code> 会话有效。</li> <li>局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 <code>存储过程和函数</code> 中使用。</li></ul> <h4 id="_1-2-2-会话用户变量"><a href="#_1-2-2-会话用户变量" class="header-anchor">#</a> 1.2.2 会话用户变量</h4> <ul><li>变量的定义</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式1：“=”或“:=”</span>
<span class="token keyword">SET</span> @用户变量 <span class="token operator">=</span> 值<span class="token punctuation">;</span>
<span class="token keyword">SET</span> @用户变量 :<span class="token operator">=</span> 值<span class="token punctuation">;</span>

<span class="token comment">#方式2：“:=” 或 INTO关键字</span>
<span class="token keyword">SELECT</span> @用户变量 :<span class="token operator">=</span> 表达式 <span class="token punctuation">[</span><span class="token keyword">FROM</span> 等子句<span class="token punctuation">]</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> 表达式 <span class="token keyword">INTO</span> @用户变量 <span class="token punctuation">[</span><span class="token keyword">FROM</span> 等子句<span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><ul><li>查看用户变量的值 （查看、比较、运算等）</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> @用户变量
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>举例</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token variable">@a</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@a</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token variable">@num</span> :<span class="token operator">=</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@num</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> <span class="token variable">@avgsalary</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@avgsalary</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token variable">@big</span><span class="token punctuation">;</span>  <span class="token comment">#查看某个未声明的变量时，将得到NULL值</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h4 id="_1-2-3-局部变量"><a href="#_1-2-3-局部变量" class="header-anchor">#</a> 1.2.3 局部变量</h4> <p>定义：可以使用 <code>DECLARE</code> 语句定义一个局部变量</p> <p>作用域：仅仅在定义它的 BEGIN ... END 中有效</p> <p>位置：只能放在 BEGIN ... END 中，而且只能放在第一句</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">BEGIN</span>
    <span class="token comment">#声明局部变量</span>
    <span class="token keyword">DECLARE</span> 变量名<span class="token number">1</span> 变量数据类型 <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span> 变量默认值<span class="token punctuation">]</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> 变量名<span class="token number">2</span><span class="token punctuation">,</span>变量名<span class="token number">3</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 变量数据类型 <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span> 变量默认值<span class="token punctuation">]</span><span class="token punctuation">;</span>
    
    <span class="token comment">#为局部变量赋值</span>
    <span class="token keyword">SET</span> 变量名<span class="token number">1</span> <span class="token operator">=</span> 值<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> 值 <span class="token keyword">INTO</span> 变量名<span class="token number">2</span> <span class="token punctuation">[</span><span class="token keyword">FROM</span> 子句<span class="token punctuation">]</span><span class="token punctuation">;</span>
    
    <span class="token comment">#查看局部变量的值</span>
    <span class="token keyword">SELECT</span> 变量<span class="token number">1</span><span class="token punctuation">,</span>变量<span class="token number">2</span><span class="token punctuation">,</span>变量<span class="token number">3</span><span class="token punctuation">;</span>
<span class="token keyword">END</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p><strong>1.定义变量</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> 变量名 类型 <span class="token punctuation">[</span><span class="token keyword">default</span> 值<span class="token punctuation">]</span><span class="token punctuation">;</span>  <span class="token comment"># 如果没有DEFAULT子句，初始值为NULL</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span>　myparam　<span class="token keyword">INT</span>　<span class="token keyword">DEFAULT</span> <span class="token number">100</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>2.变量赋值</strong></p> <p>方式1：一般用于赋简单的值</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> 变量名<span class="token operator">=</span>值<span class="token punctuation">;</span>
<span class="token keyword">SET</span> 变量名:<span class="token operator">=</span>值<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>方式2：一般用于赋表中的字段值</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 字段名或表达式 <span class="token keyword">INTO</span> 变量名 <span class="token keyword">FROM</span> 表<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>3.使用变量</strong>（查看、比较、运算等）</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 局部变量名<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例1：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> set_value<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> sal <span class="token keyword">DOUBLE</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> last_name<span class="token punctuation">,</span>salary <span class="token keyword">INTO</span> emp_name<span class="token punctuation">,</span>sal
    <span class="token keyword">FROM</span> employees
    <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> <span class="token number">102</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> emp_name<span class="token punctuation">,</span>sal<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p>举例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式1：使用用户变量</span>
<span class="token keyword">SET</span> <span class="token variable">@m</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@n</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@sum</span><span class="token operator">=</span><span class="token variable">@m</span><span class="token operator">+</span><span class="token variable">@n</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token variable">@sum</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式2：使用局部变量</span>
<span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> add_value<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token comment">#局部变量</span>
    <span class="token keyword">DECLARE</span> m <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">1</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> n <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">3</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> SUM <span class="token keyword">INT</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SET</span> SUM <span class="token operator">=</span> m<span class="token operator">+</span>n<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> SUM<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p>举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#声明</span>
<span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> different_salary<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">,</span><span class="token keyword">OUT</span> dif_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token comment">#声明局部变量</span>
    <span class="token keyword">DECLARE</span> emp_sal<span class="token punctuation">,</span>mgr_sal <span class="token keyword">DOUBLE</span> <span class="token keyword">DEFAULT</span> <span class="token number">0.0</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> mgr_id <span class="token keyword">INT</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_sal <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> manager_id <span class="token keyword">INTO</span> mgr_id <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> mgr_sal <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> mgr_id<span class="token punctuation">;</span>
    <span class="token keyword">SET</span> dif_salary <span class="token operator">=</span> mgr_sal <span class="token operator">-</span> emp_sal<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>

<span class="token comment">#调用</span>
<span class="token keyword">SET</span> <span class="token variable">@emp_id</span> <span class="token operator">=</span> <span class="token number">102</span><span class="token punctuation">;</span>
<span class="token keyword">CALL</span> different_salary<span class="token punctuation">(</span><span class="token variable">@emp_id</span><span class="token punctuation">,</span><span class="token variable">@diff_sal</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#查看</span>
<span class="token keyword">SELECT</span> <span class="token variable">@diff_sal</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br></div></div><h4 id="_1-2-4-对比会话用户变量与局部变量"><a href="#_1-2-4-对比会话用户变量与局部变量" class="header-anchor">#</a> 1.2.4 对比会话用户变量与局部变量</h4> <div class="language- line-numbers-mode"><pre class="language-text"><code>					作用域 					定义位置								     语法
会话用户变量 		  当前会话                   会话的任何地方                          加@符号，不用指定类型
局部变量  		  定义它的BEGIN END中        BEGIN END的第一句话                      一般不用加@,需要指定类型
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="_2-定义条件与处理程序"><a href="#_2-定义条件与处理程序" class="header-anchor">#</a> 2. 定义条件与处理程序</h2> <p><code>定义条件</code> 是事先定义程序执行过程中可能遇到的问题， <code>处理程序</code> 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。</p> <p>说明：定义条件和处理程序在存储过程、存储函数中都是支持的。</p> <h3 id="_2-1-案例分析"><a href="#_2-1-案例分析" class="header-anchor">#</a> 2.1 案例分析</h3> <p>案例分析：创建一个名称为“UpdateDataNoCondition”的存储过程。代码如下：</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
    BEGIN
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel';
        SET @x = 2;
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
        SET @x = 3;
    END //
    
DELIMITER ;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><p>调用存储过程：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">CALL</span> UpdateDataNoCondition<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
ERROR <span class="token number">1048</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Column</span> <span class="token string">'email'</span> cannot be <span class="token boolean">null</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@x</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token operator">|</span> <span class="token variable">@x</span>   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token operator">|</span>  <span class="token number">1</span>   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>可以看到，此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出：在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。</p> <h3 id="_2-2-定义条件"><a href="#_2-2-定义条件" class="header-anchor">#</a> 2.2 定义条件</h3> <p>定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个 <code>错误名字</code> 和 指定的<code>错误条件</code> 关联起来。这个名字可以随后被用在定义处理程序的 <code>DECLARE HANDLER</code> 语句中。定义条件使用DECLARE语句，语法格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> 错误名称 CONDITION <span class="token keyword">FOR</span> 错误码（或错误条件）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>错误码的说明：</p> <ul><li><code>MySQL_error_code</code> 和 <code>sqlstate_value</code> 都可以表示MySQL的错误。
<ul><li>MySQL_error_code是数值类型错误代码。</li> <li>sqlstate_value是长度为5的字符串类型错误代码。</li></ul></li> <li>例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。</li> <li>例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。</li></ul> <p><strong>举例1</strong>：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#使用MySQL_error_code</span>
<span class="token keyword">DECLARE</span> Field_Not_Be_NULL CONDITION <span class="token keyword">FOR</span> <span class="token number">1048</span><span class="token punctuation">;</span>

<span class="token comment">#使用sqlstate_value</span>
<span class="token keyword">DECLARE</span> Field_Not_Be_NULL CONDITION <span class="token keyword">FOR</span> SQLSTATE <span class="token string">'23000'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>举例2：定义&quot;ERROR 1148(42000)&quot;错误，名称为command_not_allowed。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#使用MySQL_error_code</span>
<span class="token keyword">DECLARE</span> command_not_allowed CONDITION <span class="token keyword">FOR</span> <span class="token number">1148</span><span class="token punctuation">;</span>

<span class="token comment">#使用sqlstate_value</span>
<span class="token keyword">DECLARE</span> command_not_allowed CONDITION <span class="token keyword">FOR</span> SQLSTATE <span class="token string">'42000'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_2-3-定义处理程序"><a href="#_2-3-定义处理程序" class="header-anchor">#</a> 2.3 定义处理程序</h3> <p>可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> 处理方式 <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> 错误类型 处理语句
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><strong>处理方式</strong>：处理方式有3个取值：CONTINUE、EXIT、UNDO。
<ul><li>CONTINUE ：表示遇到错误不处理，继续执行。</li> <li>EXIT ：表示遇到错误马上退出。</li> <li>UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。</li></ul></li> <li><strong>错误类型</strong>（即条件）可以有如下取值：
<ul><li><code>SQLSTATE</code> '字符串错误码' ：表示长度为5的sqlstate_value类型的错误代码；</li> <li><code>MySQL_error_code</code> ：匹配数值类型错误代码；</li> <li><code>错误名称</code> ：表示DECLARE ... CONDITION定义的错误条件名称。</li> <li><code>SQLWARNING</code>：匹配所有以01开头的SQLSTATE错误代码；</li> <li><code>NOT FOUND</code> ：匹配所有以02开头的SQLSTATE错误代码；</li> <li><code>SQLEXCEPTION</code> ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；</li></ul></li> <li><strong>处理语句</strong>：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 <code>BEGIN ... END</code> 编写的复合语句。</li></ul> <p>定义处理程序的几种方式，代码如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方法1：捕获sqlstate_value</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> SQLSTATE <span class="token string">'42S02'</span> <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'NO_SUCH_TABLE'</span><span class="token punctuation">;</span>

<span class="token comment">#方法2：捕获mysql_error_value</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token number">1146</span> <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'NO_SUCH_TABLE'</span><span class="token punctuation">;</span>

<span class="token comment">#方法3：先定义条件，再调用</span>
<span class="token keyword">DECLARE</span> no_such_table CONDITION <span class="token keyword">FOR</span> <span class="token number">1146</span><span class="token punctuation">;</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> NO_SUCH_TABLE <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'NO_SUCH_TABLE'</span><span class="token punctuation">;</span>

<span class="token comment">#方法4：使用SQLWARNING</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">EXIT</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> SQLWARNING <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'ERROR'</span><span class="token punctuation">;</span>

<span class="token comment">#方法5：使用NOT FOUND</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">EXIT</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token operator">NOT</span> FOUND <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'NO_SUCH_TABLE'</span><span class="token punctuation">;</span>

<span class="token comment">#方法6：使用SQLEXCEPTION</span>
<span class="token keyword">DECLARE</span> <span class="token keyword">EXIT</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> SQLEXCEPTION <span class="token keyword">SET</span> <span class="token variable">@info</span> <span class="token operator">=</span> <span class="token string">'ERROR'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br></div></div><h3 id="_2-4-案例解决"><a href="#_2-4-案例解决" class="header-anchor">#</a> 2.4 案例解决</h3> <p>在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> UpdateDataNoCondition<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token keyword">BEGIN</span>
        <span class="token comment">#定义处理程序</span>
        <span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token number">1048</span> <span class="token keyword">SET</span> <span class="token variable">@proc_value</span> <span class="token operator">=</span> <span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> email <span class="token operator">=</span> <span class="token boolean">NULL</span> <span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Abel'</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> email <span class="token operator">=</span> <span class="token string">'aabbel'</span> <span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Abel'</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token comment">//</span>
    
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><p>调用过程：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">CALL</span> UpdateDataWithCondition<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@x</span><span class="token punctuation">,</span><span class="token variable">@proc_value</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token operator">|</span> <span class="token variable">@x</span>   <span class="token operator">|</span> <span class="token variable">@proc_value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token operator">|</span>   <span class="token number">3</span>  <span class="token operator">|</span>    <span class="token operator">-</span><span class="token number">1</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p><strong>举例</strong>：
创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。</p> <p>在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行EXIT操作，并且将@proc_value的值设置为-1。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#准备工作</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> departments
<span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> atguigudb<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>departments<span class="token punctuation">`</span></span><span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> departments
<span class="token keyword">ADD</span> <span class="token keyword">CONSTRAINT</span> uk_dept_name <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>department_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> InsertDataWithCondition<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token keyword">BEGIN</span>
        <span class="token keyword">DECLARE</span> duplicate_entry CONDITION <span class="token keyword">FOR</span> SQLSTATE <span class="token string">'23000'</span> <span class="token punctuation">;</span>
        <span class="token keyword">DECLARE</span> <span class="token keyword">EXIT</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> duplicate_entry <span class="token keyword">SET</span> <span class="token variable">@proc_value</span> <span class="token operator">=</span> <span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments<span class="token punctuation">(</span>department_name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'测试'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
        <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments<span class="token punctuation">(</span>department_name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'测试'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token comment">//</span>
    
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><p>调用存储过程：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">CALL</span> InsertDataWithCondition<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.01</span> sec<span class="token punctuation">)</span>
mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@x</span><span class="token punctuation">,</span><span class="token variable">@proc_value</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token operator">|</span> <span class="token variable">@x</span>   <span class="token operator">|</span> <span class="token variable">@proc_value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token operator">|</span>   <span class="token number">2</span>  <span class="token operator">|</span>    <span class="token operator">-</span><span class="token number">1</span>       <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+-------------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h2 id="_3-流程控制"><a href="#_3-流程控制" class="header-anchor">#</a> 3. 流程控制</h2> <p>解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：</p> <ul><li><code>顺序结构</code> ：程序从上往下依次执行</li> <li><code>分支结构</code> ：程序按条件进行选择执行，从两条或多条路径中选择一条执行</li> <li><code>循环结构</code> ：程序满足一定条件下，重复执行一组语句</li></ul> <p>针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。</p> <ul><li><code>条件判断语句</code> ：IF 语句和 CASE 语句</li> <li><code>循环语句</code> ：LOOP、WHILE 和 REPEAT 语句</li> <li><code>跳转语句</code> ：ITERATE 和 LEAVE 语句</li></ul> <h3 id="_3-1-分支结构之-if"><a href="#_3-1-分支结构之-if" class="header-anchor">#</a> 3.1 分支结构之 IF</h3> <ul><li>IF 语句的语法结构是：</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">IF</span> 表达式<span class="token number">1</span> <span class="token keyword">THEN</span> 操作<span class="token number">1</span>
<span class="token punctuation">[</span><span class="token keyword">ELSEIF</span> 表达式<span class="token number">2</span> <span class="token keyword">THEN</span> 操作<span class="token number">2</span><span class="token punctuation">]</span>……
<span class="token punctuation">[</span><span class="token keyword">ELSE</span> 操作N<span class="token punctuation">]</span>
<span class="token keyword">END</span> <span class="token keyword">IF</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。</p> <ul><li><p>特点：</p> <ul><li><p>① 不同的表达式对应不同的操作</p></li> <li><p>② 使用在begin end中</p></li></ul></li> <li><p><strong>举例1</strong>：</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">IF</span> val <span class="token operator">IS</span> <span class="token boolean">NULL</span>
	<span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is null'</span><span class="token punctuation">;</span>
<span class="token keyword">ELSE</span> <span class="token keyword">SELECT</span> <span class="token string">'val is not null'</span><span class="token punctuation">;</span>

<span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>举例2：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_by_eid1<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> hire_year <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>

    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_salary <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> DATEDIFF<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>hire_date<span class="token punctuation">)</span><span class="token operator">/</span><span class="token number">365</span> <span class="token keyword">INTO</span> hire_year
    <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>

    <span class="token keyword">IF</span> emp_salary <span class="token operator">&lt;</span> <span class="token number">8000</span> <span class="token operator">AND</span> hire_year <span class="token operator">&gt;</span> <span class="token number">5</span>
    <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">500</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div><p>举例3：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_by_eid2<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> hire_year <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_salary <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> DATEDIFF<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>hire_date<span class="token punctuation">)</span><span class="token operator">/</span><span class="token number">365</span> <span class="token keyword">INTO</span> hire_year
    <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    
    <span class="token keyword">IF</span> emp_salary <span class="token operator">&lt;</span> <span class="token number">8000</span> <span class="token operator">AND</span> hire_year <span class="token operator">&gt;</span> <span class="token number">5</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">500</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
    	<span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">100</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br></div></div><p><strong>举例4</strong>：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_by_eid3<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> bonus <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_salary <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> commission_pct <span class="token keyword">INTO</span> bonus <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    
    <span class="token keyword">IF</span> emp_salary <span class="token operator">&lt;</span> <span class="token number">9000</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> <span class="token number">9000</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">ELSEIF</span> emp_salary <span class="token operator">&lt;</span> <span class="token number">10000</span> <span class="token operator">AND</span> bonus <span class="token operator">IS</span> <span class="token boolean">NULL</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> commission_pct <span class="token operator">=</span> <span class="token number">0.01</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
    	<span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">100</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>

<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br></div></div><h3 id="_3-2-分支结构之-case"><a href="#_3-2-分支结构之-case" class="header-anchor">#</a> 3.2 分支结构之 CASE</h3> <p>CASE 语句的语法结构1：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#情况一：类似于switch</span>
<span class="token keyword">CASE</span> 表达式
<span class="token keyword">WHEN</span> 值<span class="token number">1</span> <span class="token keyword">THEN</span> 结果<span class="token number">1</span>或语句<span class="token number">1</span><span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token keyword">WHEN</span> 值<span class="token number">2</span> <span class="token keyword">THEN</span> 结果<span class="token number">2</span>或语句<span class="token number">2</span><span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">ELSE</span> 结果n或语句n<span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token keyword">END</span> <span class="token punctuation">[</span><span class="token keyword">case</span><span class="token punctuation">]</span>（如果是放在<span class="token keyword">begin</span> <span class="token keyword">end</span>中需要加上<span class="token keyword">case</span>，如果放在<span class="token keyword">select</span>后面不需要）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>CASE 语句的语法结构2：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#情况二：类似于多重if</span>
<span class="token keyword">CASE</span>
<span class="token keyword">WHEN</span> 条件<span class="token number">1</span> <span class="token keyword">THEN</span> 结果<span class="token number">1</span>或语句<span class="token number">1</span><span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token keyword">WHEN</span> 条件<span class="token number">2</span> <span class="token keyword">THEN</span> 结果<span class="token number">2</span>或语句<span class="token number">2</span><span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">ELSE</span> 结果n或语句n<span class="token punctuation">(</span>如果是语句，需要加分号<span class="token punctuation">)</span>
<span class="token keyword">END</span> <span class="token punctuation">[</span><span class="token keyword">case</span><span class="token punctuation">]</span>（如果是放在<span class="token keyword">begin</span> <span class="token keyword">end</span>中需要加上<span class="token keyword">case</span>，如果放在<span class="token keyword">select</span>后面不需要）
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p><strong>举例1</strong>：
使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CASE</span> val
　　　<span class="token keyword">WHEN</span> <span class="token number">1</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is 1'</span><span class="token punctuation">;</span>
　　　<span class="token keyword">WHEN</span> <span class="token number">2</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is 2'</span><span class="token punctuation">;</span>
　　　<span class="token keyword">ELSE</span> <span class="token keyword">SELECT</span> <span class="token string">'val is not 1 or 2'</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">CASE</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><strong>举例2</strong>：
使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CASE</span>
    <span class="token keyword">WHEN</span> val <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is null'</span><span class="token punctuation">;</span>
    <span class="token keyword">WHEN</span> val <span class="token operator">&lt;</span> <span class="token number">0</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is less than 0'</span><span class="token punctuation">;</span>
    <span class="token keyword">WHEN</span> val <span class="token operator">&gt;</span> <span class="token number">0</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token string">'val is greater than 0'</span><span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span> <span class="token keyword">SELECT</span> <span class="token string">'val is 0'</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token keyword">CASE</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>举例3：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_by_eid4<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_sal <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> bonus <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_sal <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> commission_pct <span class="token keyword">INTO</span> bonus <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    
    <span class="token keyword">CASE</span>
    <span class="token keyword">WHEN</span> emp_sal<span class="token operator">&lt;</span><span class="token number">9000</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span><span class="token number">9000</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">WHEN</span> emp_sal<span class="token operator">&lt;</span><span class="token number">10000</span> <span class="token operator">AND</span> bonus <span class="token operator">IS</span> <span class="token boolean">NULL</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> commission_pct<span class="token operator">=</span><span class="token number">0.01</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
    	<span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">100</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">CASE</span><span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br></div></div><p>举例4：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_by_eid5<span class="token punctuation">(</span><span class="token operator">IN</span> emp_id <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> emp_sal <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> hire_year <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> salary <span class="token keyword">INTO</span> emp_sal <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> <span class="token function">ROUND</span><span class="token punctuation">(</span>DATEDIFF<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>hire_date<span class="token punctuation">)</span><span class="token operator">/</span><span class="token number">365</span><span class="token punctuation">)</span> <span class="token keyword">INTO</span> hire_year <span class="token keyword">FROM</span> employees
    <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    
    <span class="token keyword">CASE</span> hire_year
        <span class="token keyword">WHEN</span> <span class="token number">0</span> <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">50</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
        <span class="token keyword">WHEN</span> <span class="token number">1</span> <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">100</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
        <span class="token keyword">WHEN</span> <span class="token number">2</span> <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">200</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
        <span class="token keyword">WHEN</span> <span class="token number">3</span> <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">300</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
        <span class="token keyword">WHEN</span> <span class="token number">4</span> <span class="token keyword">THEN</span> <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">400</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span> 
    	<span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary<span class="token operator">=</span>salary<span class="token operator">+</span><span class="token number">500</span> <span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> emp_id<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">CASE</span><span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br></div></div><h3 id="_3-3-循环结构之loop"><a href="#_3-3-循环结构之loop" class="header-anchor">#</a> 3.3 循环结构之LOOP</h3> <p>LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。</p> <p>LOOP语句的基本格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token punctuation">[</span>loop_label:<span class="token punctuation">]</span> <span class="token keyword">LOOP</span>
循环执行的语句
<span class="token keyword">END</span> <span class="token keyword">LOOP</span> <span class="token punctuation">[</span>loop_label<span class="token punctuation">]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>其中，loop_label表示LOOP语句的标注名称，该参数可以省略。
<strong>举例1</strong>：
使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> id <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
add_loop:<span class="token keyword">LOOP</span>
    <span class="token keyword">SET</span> id <span class="token operator">=</span> id <span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> id <span class="token operator">&gt;=</span> <span class="token number">10</span> <span class="token keyword">THEN</span> <span class="token keyword">LEAVE</span> add_loop<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token keyword">LOOP</span> add_loop<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p><strong>举例2</strong>：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_loop<span class="token punctuation">(</span><span class="token keyword">OUT</span> num <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> avg_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> loop_count <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_salary <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    
    label_loop:<span class="token keyword">LOOP</span>
        <span class="token keyword">IF</span> avg_salary <span class="token operator">&gt;=</span> <span class="token number">12000</span> <span class="token keyword">THEN</span> <span class="token keyword">LEAVE</span> label_loop<span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">*</span> <span class="token number">1.1</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> loop_count <span class="token operator">=</span> loop_count <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_salary <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">LOOP</span> label_loop<span class="token punctuation">;</span>
    
    <span class="token keyword">SET</span> num <span class="token operator">=</span> loop_count<span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br></div></div><h3 id="_3-4-循环结构之while"><a href="#_3-4-循环结构之while" class="header-anchor">#</a> 3.4 循环结构之WHILE</h3> <p>WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token punctuation">[</span>while_label:<span class="token punctuation">]</span> <span class="token keyword">WHILE</span> 循环条件  <span class="token keyword">DO</span>
循环体
<span class="token keyword">END</span> <span class="token keyword">WHILE</span> <span class="token punctuation">[</span>while_label<span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。</p> <p><strong>举例1</strong>：</p> <p>WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> test_while<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    <span class="token keyword">WHILE</span> i <span class="token operator">&lt;</span> <span class="token number">10</span> <span class="token keyword">DO</span>
    	<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> i<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>

<span class="token comment">#调用</span>
<span class="token keyword">CALL</span> test_while<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div><p><strong>举例2</strong>：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_while<span class="token punctuation">(</span><span class="token keyword">OUT</span> num <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> avg_sal <span class="token keyword">DOUBLE</span> <span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> while_count <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    
    <span class="token keyword">WHILE</span> avg_sal <span class="token operator">&gt;</span> <span class="token number">5000</span> <span class="token keyword">DO</span>
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">*</span> <span class="token number">0.9</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> while_count <span class="token operator">=</span> while_count <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SET</span> num <span class="token operator">=</span> while_count<span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><h3 id="_3-5-循环结构之repeat"><a href="#_3-5-循环结构之repeat" class="header-anchor">#</a> 3.5 循环结构之REPEAT</h3> <p>REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。</p> <p>REPEAT语句的基本格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token punctuation">[</span>repeat_label:<span class="token punctuation">]</span> <span class="token keyword">REPEAT</span>
　　　　循环体的语句
UNTIL 结束循环的条件表达式
<span class="token keyword">END</span> <span class="token keyword">REPEAT</span> <span class="token punctuation">[</span>repeat_label<span class="token punctuation">]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。</p> <p><strong>举例1</strong>：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> test_repeat<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> i <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    <span class="token keyword">REPEAT</span>
    	<span class="token keyword">SET</span> i <span class="token operator">=</span> i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
    UNTIL i <span class="token operator">&gt;=</span> <span class="token number">10</span>
    <span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> i<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><p><strong>举例2</strong>：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> update_salary_repeat<span class="token punctuation">(</span><span class="token keyword">OUT</span> num <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> avg_sal <span class="token keyword">DOUBLE</span> <span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> repeat_count <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    
    <span class="token keyword">REPEAT</span>
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">*</span> <span class="token number">1.15</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> repeat_count <span class="token operator">=</span> repeat_count <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    UNTIL avg_sal <span class="token operator">&gt;=</span> <span class="token number">13000</span>
    <span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SET</span> num <span class="token operator">=</span> repeat_count<span class="token punctuation">;</span>
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br></div></div><p><strong>对比三种循环结构：</strong></p> <p>1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。</p> <p>2、 LOOP：一般用于实现简单的&quot;死&quot;循环 WHILE：先判断后执行 REPEAT：先执行后判断，无条件至少执行一次</p> <h3 id="_3-6-跳转语句之leave语句"><a href="#_3-6-跳转语句之leave语句" class="header-anchor">#</a> 3.6 跳转语句之LEAVE语句</h3> <p>LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。</p> <p>基本格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LEAVE</span> 标记名
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。</p> <p><strong>举例1</strong>：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。</p> <ul><li>如果num&lt;=0，则使用LEAVE语句退出BEGIN...END；</li> <li>如果num=1，则查询“employees”表的平均薪资；</li> <li>如果num=2，则查询“employees”表的最低薪资；</li> <li>如果num&gt;2，则查询“employees”表的最高薪资。</li></ul> <p>IF语句结束后查询“employees”表的总人数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> leave_begin<span class="token punctuation">(</span><span class="token operator">IN</span> num <span class="token keyword">INT</span><span class="token punctuation">)</span>

begin_label: <span class="token keyword">BEGIN</span>
    <span class="token keyword">IF</span> num<span class="token operator">&lt;=</span><span class="token number">0</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">LEAVE</span> begin_label<span class="token punctuation">;</span>
    <span class="token keyword">ELSEIF</span> num<span class="token operator">=</span><span class="token number">1</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">ELSEIF</span> num<span class="token operator">=</span><span class="token number">2</span>
    	<span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
    	<span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
   
   <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><p><strong>举例2</strong>：</p> <p>当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“leave_while()”，声明OUT参数num，输出循环次数，存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> leave_while<span class="token punctuation">(</span><span class="token keyword">OUT</span> num <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token comment">#</span>
    <span class="token keyword">DECLARE</span> avg_sal <span class="token keyword">DOUBLE</span><span class="token punctuation">;</span><span class="token comment">#记录平均工资</span>
    <span class="token keyword">DECLARE</span> while_count <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment">#记录循环次数</span>
    
    <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span> <span class="token comment">#① 初始化条件</span>
    
    while_label:<span class="token keyword">WHILE</span> <span class="token boolean">TRUE</span> <span class="token keyword">DO</span>  <span class="token comment">#② 循环条件</span>
    
        <span class="token comment">#③ 循环体</span>
        <span class="token keyword">IF</span> avg_sal <span class="token operator">&lt;=</span> <span class="token number">10000</span> <span class="token keyword">THEN</span>
        	<span class="token keyword">LEAVE</span> while_label<span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
        <span class="token keyword">UPDATE</span> employees <span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">*</span> <span class="token number">0.9</span><span class="token punctuation">;</span>
        <span class="token keyword">SET</span> while_count <span class="token operator">=</span> while_count <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        
        <span class="token comment">#④ 迭代条件</span>
        <span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">INTO</span> avg_sal <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
    
    <span class="token comment">#赋值</span>
    <span class="token keyword">SET</span> num <span class="token operator">=</span> while_count<span class="token punctuation">;</span>

<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br></div></div><h3 id="_3-7-跳转语句之iterate语句"><a href="#_3-7-跳转语句之iterate语句" class="header-anchor">#</a> 3.7 跳转语句之ITERATE语句</h3> <p>ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。</p> <p>语句基本格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ITERATE</span> label
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。</p> <p><strong>举例</strong>： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。</p> <ul><li>如果num &lt; 10，则继续执行循环；</li> <li>如果num &gt; 15，则退出循环结构；</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> test_iterate<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> num <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
    
    my_loop:<span class="token keyword">LOOP</span>
        <span class="token keyword">SET</span> num <span class="token operator">=</span> num <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">IF</span> num <span class="token operator">&lt;</span> <span class="token number">10</span>
        	<span class="token keyword">THEN</span> <span class="token keyword">ITERATE</span> my_loop<span class="token punctuation">;</span>
        <span class="token keyword">ELSEIF</span> num <span class="token operator">&gt;</span> <span class="token number">15</span>
        	<span class="token keyword">THEN</span> <span class="token keyword">LEAVE</span> my_loop<span class="token punctuation">;</span>
        <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
        
        <span class="token keyword">SELECT</span> <span class="token string">'尚硅谷：让天下没有难学的技术'</span><span class="token punctuation">;</span>
        
    <span class="token keyword">END</span> <span class="token keyword">LOOP</span> my_loop<span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br></div></div><h2 id="_4-游标"><a href="#_4-游标" class="header-anchor">#</a> 4. 游标</h2> <h3 id="_4-1-什么是游标-或光标"><a href="#_4-1-什么是游标-或光标" class="header-anchor">#</a> 4.1 什么是游标（或光标）</h3> <p>虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是 <code>随意定位到某一条记录</code> ，并对记录的数据进行处理。
这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。<strong>游标让 SQL 这种面向集合的语言有了面向过程开发的能力。</strong></p> <p>在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标 <code>充当了指针的作用</code> ，我们可以通过操作游标来对数据行进行操作。</p> <p>MySQL中游标可以在存储过程和函数中使用。</p> <p>比如，我们查询了 employees 数据表中工资高于15000的员工都有哪些：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span>last_name<span class="token punctuation">,</span>salary <span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> salary <span class="token operator">&gt;</span> <span class="token number">15000</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251930492.png" alt="image-20220613083224786"></p> <p>这里我们就可以通过游标来操作数据行，如图所示此时游标所在的行是“108”的记录，我们也可以在结果集上滚动游标，指向结果集中的任意一行。</p> <h3 id="_4-2-使用游标步骤"><a href="#_4-2-使用游标步骤" class="header-anchor">#</a> 4.2 使用游标步骤</h3> <p>游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。如果我们想要使用游标，一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。</p> <p><strong>第一步，声明游标</strong>
在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> cursor_name <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> select_statement<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要写成：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> cursor_name <span class="token keyword">CURSOR</span> <span class="token operator">IS</span> select_statement<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。</p> <p>比如：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> cur_emp <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span>
<span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span>salary <span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DECLARE</span> cursor_fruit <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span>
<span class="token keyword">SELECT</span> f_name<span class="token punctuation">,</span> f_price <span class="token keyword">FROM</span> fruits <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>第二步，打开游标</strong></p> <p>打开游标的语法如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">OPEN</span> cursor_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">OPEN</span>　cur_emp <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>第三步，使用游标（从游标中取得数据）</strong></p> <p>语法如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">FETCH</span> cursor_name <span class="token keyword">INTO</span> var_name <span class="token punctuation">[</span><span class="token punctuation">,</span> var_name<span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。</p> <p>注意：var_name必须在声明游标之前就定义好。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">FETCH</span>　cur_emp <span class="token keyword">INTO</span> emp_id<span class="token punctuation">,</span> emp_sal <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>注意：<strong>游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致</strong>，否则，在存储过程执行的时候，MySQL 会提示错误。</p> <p><strong>第四步，关闭游标</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CLOSE</span> cursor_name
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会<code>占用系统资源</code> ，如果不及时关闭，<strong>游标会一直保持到存储过程结束</strong>，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。</p> <p>关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CLOSE</span>　cur_emp<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_4-3-举例"><a href="#_4-3-举例" class="header-anchor">#</a> 4.3 举例</h3> <p>创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> <span class="token comment">//</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> get_count_by_limit_total_salary<span class="token punctuation">(</span><span class="token operator">IN</span> limit_total_salary <span class="token keyword">DOUBLE</span><span class="token punctuation">,</span><span class="token keyword">OUT</span> total_count <span class="token keyword">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> sum_salary <span class="token keyword">DOUBLE</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>  <span class="token comment">#记录累加的总工资</span>
    <span class="token keyword">DECLARE</span> cursor_salary <span class="token keyword">DOUBLE</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment">#记录某一个工资值</span>
    <span class="token keyword">DECLARE</span> emp_count <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment">#记录循环个数</span>
    
    <span class="token comment">#定义游标</span>
    <span class="token keyword">DECLARE</span> emp_cursor <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> <span class="token keyword">SELECT</span> salary <span class="token keyword">FROM</span> employees <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span><span class="token punctuation">;</span>
    
    <span class="token comment">#打开游标</span>
    <span class="token keyword">OPEN</span> emp_cursor<span class="token punctuation">;</span>
    
    <span class="token keyword">REPEAT</span>
        <span class="token comment">#使用游标（从游标中获取数据）</span>
        <span class="token keyword">FETCH</span> emp_cursor <span class="token keyword">INTO</span> cursor_salary<span class="token punctuation">;</span>
        
        <span class="token keyword">SET</span> sum_salary <span class="token operator">=</span> sum_salary <span class="token operator">+</span> cursor_salary<span class="token punctuation">;</span>
        <span class="token keyword">SET</span> emp_count <span class="token operator">=</span> emp_count <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        
        UNTIL sum_salary <span class="token operator">&gt;=</span> limit_total_salary
    <span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
    
    <span class="token keyword">SET</span> total_count <span class="token operator">=</span> emp_count<span class="token punctuation">;</span>
    
    <span class="token comment">#关闭游标</span>
    <span class="token keyword">CLOSE</span> emp_cursor<span class="token punctuation">;</span>
    
<span class="token keyword">END</span> <span class="token comment">//</span>

<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br></div></div><h3 id="_4-5-小结"><a href="#_4-5-小结" class="header-anchor">#</a> 4.5 小结</h3> <p>游标是 MySQL 的一个重要的功能，为 <code>逐条读取</code> 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。</p> <p>但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 <code>加锁</code> ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 <code>消耗系统资源</code> ，造成内存不足，这是因为游标是在内存中进行的处理。</p> <p>建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。</p> <h2 id="补充-mysql-8-0的新特性-全局变量的持久化"><a href="#补充-mysql-8-0的新特性-全局变量的持久化" class="header-anchor">#</a> 补充：MySQL 8.0的新特性—全局变量的持久化</h2> <p>在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> MAX_EXECUTION_TIME<span class="token operator">=</span><span class="token number">2000</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>使用SET GLOBAL语句设置的变量值只会 <code>临时生效</code> 。 <code>数据库重启</code> 后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了 <code>SET PERSIST</code> 命令。例如，设置服务器的最大连接数为1000：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> PERSIST <span class="token keyword">global</span> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>MySQL会将该命令的配置保存到数据目录下的 <code>mysqld-auto.cnf</code> 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。</p> <p>举例：</p> <p>查看全局变量max_connections的值，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%max_connections%'</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token operator">|</span> Variable_name          <span class="token operator">|</span> <span class="token keyword">Value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token operator">|</span> max_connections        <span class="token operator">|</span> <span class="token number">151</span>   <span class="token operator">|</span>
<span class="token operator">|</span> mysqlx_max_connections <span class="token operator">|</span> <span class="token number">100</span>   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span><span class="token punctuation">,</span> <span class="token number">1</span> warning <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>设置全局变量max_connections的值：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">set</span> persist max_connections<span class="token operator">=</span><span class="token number">1000</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><code>重启MySQL服务器</code> ，再次查询max_connections的值：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%max_connections%'</span><span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token operator">|</span> Variable_name          <span class="token operator">|</span> <span class="token keyword">Value</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token operator">|</span> max_connections        <span class="token operator">|</span> <span class="token number">1000</span>  <span class="token operator">|</span>
<span class="token operator">|</span> mysqlx_max_connections <span class="token operator">|</span> <span class="token number">100</span>   <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------------------------+-------+</span>
<span class="token number">2</span> <span class="token keyword">rows</span> <span class="token operator">in</span> <span class="token keyword">set</span><span class="token punctuation">,</span> <span class="token number">1</span> warning <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/16.变量与流程控制与游标.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/e5e604/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">存储过程与函数</div></a> <a href="/noteslibrary/pages/fe941e/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">触发器</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/e5e604/" class="prev">存储过程与函数</a></span> <span class="next"><a href="/noteslibrary/pages/fe941e/">触发器</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/103.b0e7acf6.js" defer></script>
  </body>
</html>
